﻿using System;
using System.Collections.Generic;
using System.Text;
using XLCS.Common;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Windows.Forms;
using CoreEA;
using WPF_DbCenter.DbEnginer;
using EE.SqlCe;
using System.IO;

namespace WPF_DbCenter.CommonMethod
{
    internal class CEEnginer : IDbEnginer
    {
        public string CurPwd { get; set; }

        private string _curOpeningDbName = string.Empty;
        public string CurOpeningDbName
        {
            get
            {
                return _curOpeningDbName;
            }
        }

        public CEEnginer()
        {
            _customHandler = new CoreE(CoreEA.CoreE.UsedDatabaseType.SqlCE35).X_Handler;
        }

        private Database _dbHandler = null;
        Database IDbEnginer.DbHandler
        {
            get
            {
                if (_dbHandler == null)
                {
                    throw new Exception("Handle has not created,please open database first");

                }
                return _dbHandler;
            }
        }

        private List<string> _tableList = null;
        public List<string> TableList
        {
            get
            {
                return _tableList;
            }
        }

        private readonly int SqlCeTableName_MaxLength = 128;

        ICoreEAHander _customHandler=null;

        ICoreEAHander IDbEnginer.CustomHandler
        {
            get
            {
                return _customHandler;

            }
        }


        //+		["DATE_MODIFIED"]	{DATE_MODIFIED}	
        //+		["TABLE_TYPE"]	{TABLE_TYPE}	
        //+		["DESCRIPTION"]	{DESCRIPTION}	
        //+		["DATE_CREATED"]	{DATE_CREATED}	
        //+		["TABLE_PROPID"]	{TABLE_PROPID}	
        //+		["TABLE_SCHEMA"]	{TABLE_SCHEMA}	
        //+		["TABLE_GUID"]	{TABLE_GUID}	
        //+		["TABLE_CATALOG"]	{TABLE_CATALOG}	
        //+		["TABLE_NAME"]	{TABLE_NAME}	
        private readonly string _getTableList = "SELECT * FROM INFORMATION_SCHEMA.TABLES";

        /// <summary>
        /// Get all infos from schemalinfo
        /// </summary>
        private readonly string _getIndexesSysInfo = "INFORMATION_SCHEMA.INDEXES";
        private readonly string _getColumnsSysInfo = "INFORMATION_SCHEMA.COLUMNS";

        private readonly string TableNameMask = "TABLE_NAME";
        private readonly string ColumnNameMask = "COLUMN_NAME";
        private readonly string IndexNameMask = "INDEX_NAME";
        private readonly string keyColumnUsageMask = "INFORMATION_SCHEMA.KEY_COLUMN_USAGE";
        private readonly string providerTypeMask = "INFORMATION_SCHEMA.PROVIDER_TYPES";

        //private readonly string referentialConstMask = "INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS";
        private string GetSqlCEConnectionString(string dbFileFullPath, string pwd, OpenModeClass curOpenMode)
        {
            string result = string.Empty;
            if (string.IsNullOrEmpty(dbFileFullPath))
            {
                throw new Exception("Databse file path is not valid");
            }

            result = string.Format("Data Source={0}", dbFileFullPath);

            if (!string.IsNullOrEmpty(pwd))
            {
                result = string.Format("{0}; Password={1}", result, pwd);
            }
            switch (curOpenMode.mode)
            {
                case OpenMode.ReadWrite:
                    result = string.Format("{0}; File Mode={1}", result, curOpenMode.modeDisplayName);
                    break;
                case OpenMode.ReadOnly:
                    result = string.Format("{0}; File Mode={1};Temp Path=%temp%", result, curOpenMode.modeDisplayName);
                    break;
                case OpenMode.Exclusive:
                    break;
                case OpenMode.SharedRead:
                    break;
                default:
                    result = string.Format("{0}; File Mode={1}", result, curOpenMode.modeDisplayName);
                    break;
            }
            

            return result;
        }

        private void ValidateConnection()
        {
            if (_dbHandler == null)
            {
                throw new Exception("Database has not opened");
            }
        }

        bool IDbEnginer.CreateTableWithSchemaInfo(object args, string tableName)
        {
            bool result = false;
            List<CreateTableArgs> _args = args as List<CreateTableArgs>;
            if (_args == null)
            {
                return false;
            }

            ValidateConnection();
            string cmd = string.Empty;
            try
            {

                //Create table at least need one column 
                //so here add a temp column
                cmd = String.Format("CREATE TABLE {0} (", tableName);
                string t1=string.Empty;
                string t2 = string.Empty;
                string t3 = string.Empty;
                string t4 = string.Empty;
                string t7 = string.Empty;

                string stepCmd = string.Empty;

                foreach (CreateTableArgs item in _args)
                {
                    if (item.dataLength > 0)
                    {
                        if (item.dataLength == 0)
                        {
                            t4 = "";
                        }
                        else
                        {
                            t4 = string.Format("({0})", item.dataLength);
                        }
                    }

                    t1 = item.allowNulls == true ? "NULL" : "NOT NULL";
                    t2 = item.isUnique == true ? "UNIQUE" : "";
                    t3 = item.isPrimaryKey == true ? "PRIMARY KEY" : "";
                    if (item.isPrimaryKey)
                    {
                        t7="CONSTRAINT";
                    }

                    stepCmd = String.Format("{0} {1} {2}{3} {7} {4} {5} {6},", stepCmd,item.fieldName, item.dataType, t4,
                       t7, t1,t2,t3);

                }
                stepCmd = stepCmd.Substring(0, stepCmd.Length - 1);
                stepCmd += ")";

                cmd += stepCmd;
                DbCommand dbCmd = _dbHandler.GetSqlStringCommand(cmd);
                
                _dbHandler.ExecuteNonQuery(dbCmd);

                result = true;
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
            }


            return result;
        }
        int IDbEnginer.GetTableMaxLength
        {
            get
            {
                return SqlCeTableName_MaxLength;
            }
        }
        bool IDbEnginer.ExecuteSimpleCommand(string cmd)
        {

            try
            {
                DbCommand dbCmd = _dbHandler.GetSqlStringCommand(cmd);
                _dbHandler.ExecuteNonQuery(dbCmd);
                return true;
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
                return false;
            }

        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<string> GetTableList()
        {
            List<string> result = null; ;
            ValidateConnection();

        ReOpen:
            try
            {
                DbCommand cmd = _dbHandler.GetSqlStringCommand(_getTableList);
                DataSet tempDs = _dbHandler.ExecuteDataSet(cmd);

                if ((tempDs != null) && (tempDs.Tables.Count > 0))
                {
                    result = new List<string>();
                    foreach (DataRow dr in tempDs.Tables[0].Rows)
                    {
                        result.Add(dr[TableNameMask].ToString());
                    }
                }

                _tableList = result;
            }
            catch (SqlCeInvalidDatabaseFormatException ceFormat)
            {
                if (DialogResult.Yes == MessageBox.Show("Your database is old version ,do you want to upgrade it ,so this software can modify it?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1))
                {
                    if (Properties.Settings.Default.AutoBackupOldVersionFileBeforeUpgrade)
                    {
                        File.Copy(_curOpeningDbName, Config.AutoBackupFolder + Path.GetFileName(_curOpeningDbName),true);
                    }
                    //if the db is 3.1 version ,need call upgrade
                    if (ceFormat.Message.Contains("Upgrade()"))
                    {
                        _dbHandler.Upgrade();
                        goto ReOpen;
                    }
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ce)
            {
                ProcessException.DisplayErrors(ce);
                result = null;
            }
        return result;
        }

        bool IDbEnginer.CreateDataBase(string dbName, string pwd, bool Encrypt)
        {
            bool result = false;

            string createTabelCommand = string.Empty;
            //Use "" for filter speical Chars
            createTabelCommand = string.Format("DataSource=\"{0}\"", dbName);

            if (!string.IsNullOrEmpty(pwd))
            {
                createTabelCommand = string.Format("{0} ;Password={1}", createTabelCommand, @pwd);
            }

            createTabelCommand = string.Format("{0} ;Encrypt={1}", createTabelCommand, Encrypt);

            try
            {
                SqlCeDatabase s = new SqlCeDatabase(createTabelCommand);

                s.CreateFile();
                result = true;
            }
            catch (Exception ce)
            {
                ProcessException.DisplayErrors(ce);
                result = false;
            }

            return result;

        }

        bool IDbEnginer.CreateDataBase(string dbName, string pwd)
        {
            IDbEnginer i = this as IDbEnginer;
            
            return i.CreateDataBase(dbName, pwd, false);
        }

        bool IDbEnginer.CreateDataBase(string dbName)
        {
            IDbEnginer i = this as IDbEnginer;
            return i.CreateDataBase(dbName, string.Empty, false);
        }

        /// <summary>
        /// Get all columns info
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataSet GetColumnInfoFromTable(string tableName)
        {
            ValidateConnection();
            DataSet tempDs = null;
            try
            {
                DbCommand cmd = _dbHandler.GetSqlStringCommand(
                string.Format("SELECT * FROM {0} where TABLE_NAME=?", _getColumnsSysInfo));
                _dbHandler.AddParameter(cmd, "TABLE_NAME", DbType.String, ParameterDirection.Input, "TABLE_NAME", DataRowVersion.Default, tableName);

                tempDs = _dbHandler.ExecuteDataSet(cmd);
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
            }
            return tempDs;
        }

        List<string> IDbEnginer.GetColumnNameListFromTable(string tableName)
        {
            ValidateConnection();
            DataSet tempDs = GetColumnInfoFromTable(tableName);
            List<string> columnList = new List<string>();
            if ((tempDs != null) && (tempDs.Tables.Count > 0))
            {
                foreach (DataRow item in tempDs.Tables[0].Rows)
                {
                    columnList.Add(item[ColumnNameMask].ToString());
                }
            }

            return columnList;
        }

        bool IDbEnginer.InsertData(string tableName, List<string> columnName, List<string> columnValue, List<DbType> types)
        {
            ValidateConnection();
            bool result = false;

            try
            {
                StringBuilder st = new StringBuilder();
                StringBuilder st2 = new StringBuilder();

                int count = columnName.Count;
                int j = 0;
                foreach (string item in columnName)
                {
                    st.Append(item);

                    st2.Append("?");

                    j++;
                    if (j < count)
                    {
                        st.Append(",");
                        st2.Append(",");
                    }
                }

                using (DbCommand cmd = _dbHandler.DbProviderFactory.CreateCommand())
                {
                    cmd.CommandText = string.Format("Insert into {0} {1} values ({2})", tableName, st.ToString(), st2.ToString());
                    for (int i = 0; i < columnName.Count; i++)
                    {
                        _dbHandler.AddInParameter(cmd, columnName[i], types[i], columnValue[i]);
                    }

                    cmd.ExecuteNonQuery();
                }
                result = true;
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
            }
            return result;
        }
        /// <summary>
        /// Get all indexes info 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        DataSet IDbEnginer.GetIndexInfoFromTable(string tableName)
        {
            ValidateConnection();
            DataSet tempDs = null;
            try
            {
                DbCommand cmd = _dbHandler.GetSqlStringCommand(
                    string.Format("SELECT * FROM {0} where TABLE_NAME=?", _getIndexesSysInfo));
                _dbHandler.AddParameter(cmd, "TABLE_NAME", DbType.String, ParameterDirection.Input, "TABLE_NAME", DataRowVersion.Default, tableName);

                tempDs = _dbHandler.ExecuteDataSet(cmd);
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
            }
            return tempDs;
        }

        List<string> IDbEnginer.GetIndexNameListFromTable(string tableName)
        {
            DataSet tempDs = GetColumnInfoFromTable(tableName);
            List<string> columnList = new List<string>();
            if ((tempDs != null) && (tempDs.Tables.Count > 0))
            {
                foreach (DataRow item in tempDs.Tables[0].Rows)
                {
                    columnList.Add(item[IndexNameMask].ToString());
                }
            }

            return columnList;
        }

        /// <summary>
        /// Get all info 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        DataSet IDbEnginer.GetKeyColumnInfoFromTable(string tableName)
        {
            ValidateConnection();
            DataSet tempDs = null;
            try
            {
                DbCommand cmd = _dbHandler.GetSqlStringCommand(
                    string.Format("SELECT * FROM {0} where TABLE_NAME=?", keyColumnUsageMask));
                _dbHandler.AddParameter(cmd, "TABLE_NAME", DbType.String, ParameterDirection.Input, "TABLE_NAME", DataRowVersion.Default, tableName);
                tempDs = _dbHandler.ExecuteDataSet(cmd);
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
            }
            return tempDs;
        }


        /// <summary>
        /// Get all  info 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        DataSet IDbEnginer.GetProviderInfoFromTable(string tableName)
        {
            ValidateConnection();
            //where TABLE_NAME='{1}'
            DataSet tempDs = null;
            try
            {
                DbCommand cmd = _dbHandler.GetSqlStringCommand(
                    string.Format("SELECT * FROM {0} ", providerTypeMask));
                tempDs = _dbHandler.ExecuteDataSet(cmd);
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
            }
            return tempDs;
        }

        /// <summary>
        /// Get all data type supported by sqlce 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        DataSet IDbEnginer.GetSupportedDbType()
        {
            ValidateConnection();
            DataSet tempDs = null;
            try
            {
                //where TABLE_NAME='{1}'
                DbCommand cmd = _dbHandler.GetSqlStringCommand(
                string.Format("SELECT TYPE_NAME FROM {0} ", providerTypeMask));
                tempDs = _dbHandler.ExecuteDataSet(cmd);
            }
            catch (Exception ee)
            {
                ProcessException.DisplayErrors(ee);
            }
            return tempDs;
        }

        bool IDbEnginer.OpenDatabase(string dbName, string pwd, OpenModeClass curOpenMode)
        {
            _dbHandler = new SqlCeDatabase(GetSqlCEConnectionString(dbName, pwd,curOpenMode));
            
            _curOpeningDbName = dbName;
            return GetTableList() != null;
        }

        #region IDisposable Members

        void IDisposable.Dispose()
        {
            if (_customHandler != null)
            {
                if (_customHandler.IsOpened)
                {
                    _customHandler.Close();
                }
            }
           
        }

        #endregion


        #region IDbEnginer Members

        bool IDbEnginer.OpenDatabase(string dbName, string pwd)
        {
            return ((IDbEnginer)this).OpenDatabase(dbName, pwd, new OpenModeClass() { mode = OpenMode.ReadWrite });
        }

        bool IDbEnginer.OpenDatabase(string dbName)
        {
            return ((IDbEnginer)this).OpenDatabase(dbName, string.Empty, new OpenModeClass() { mode = OpenMode.ReadWrite });
        }

        #endregion
    }
}
